package com.katian;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

/**
 * User: csavard
 * Date: 13-05-20
 */
public class MergeDeShnout {
    public static void main(String[] args) throws IOException {
        InputStream destinationInput = new FileInputStream("C:\\Users\\csavard\\Downloads\\Dépenses  2012 (Récupéré).xlsx");
        InputStream sourceInput = new FileInputStream("C:\\Users\\csavard\\Downloads\\Guide Francoeur déc 2012 (Enregistré automatiquement) - Copie.xlsx");

        System.out.println("Loading destination");
        XSSFWorkbook destinationWb = new XSSFWorkbook(destinationInput);

        System.out.println("Loading source");
        XSSFWorkbook sourceWb = new XSSFWorkbook(sourceInput);

        System.out.println("Merge");
        Sheet destinationSheet = destinationWb.getSheet("Détail des coût à date");
        Sheet sourceSheet = sourceWb.getSheet("Détail des coût à date");
        int destinationRowIndex = 1;
        int sourceRowIndex = 1;
        while (sourceRowIndex <= sourceSheet.getLastRowNum()) {
            Row sourceRow = sourceSheet.getRow(sourceRowIndex);
            Cell sectorCEll = sourceRow.getCell(18);
            if (sectorCEll != null) {
                String sector = sectorCEll.getStringCellValue();
                if (sector.equals("11") || sector.equals("12") || sector.equals("13") || sector.equals("14") || sector.equals("15") || sector.equals("16") || sector.equals("17") || sector.equals("18")) {
                    Row destinationRow = destinationSheet.getRow(destinationRowIndex);
                    if(destinationRow == null){
                        destinationRow = destinationSheet.createRow(destinationRowIndex);
                    }
                    int columnIndex = 0;
                    while (columnIndex <= sourceRow.getLastCellNum()) {
                        XSSFCell sourceCell = (XSSFCell) sourceRow.getCell(columnIndex);
                        if (sourceCell != null) {
                            XSSFCell destinationCell = (XSSFCell) destinationRow.getCell(columnIndex);
                            if(destinationCell == null){
                                destinationRow.createCell(columnIndex);
                                destinationCell =  (XSSFCell) destinationRow.getCell(columnIndex);
                            }
                            copyCell(sourceCell, destinationCell);
                        }
                        columnIndex++;
                    }
                    destinationRowIndex++;
                }
            }
            sourceRowIndex++;
        }

        File file = new File("C:\\Users\\csavard\\Downloads\\Dépenses  2012 (Cool).xlsx");

        System.out.println("Write file to: " + file.getAbsolutePath());
        destinationWb.write(new FileOutputStream(file));
    }

    private static void copyCell(XSSFCell sourceCEll, XSSFCell destinationCell) {
        switch (sourceCEll.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                destinationCell.setCellValue(sourceCEll.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                destinationCell.setCellValue(sourceCEll.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                destinationCell.setCellValue(XSSFCell.CELL_TYPE_BLANK);
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                destinationCell.setCellValue(sourceCEll.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                destinationCell.setCellFormula(sourceCEll.getCellFormula());
                break;
        }
    }
}
